﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO


Public Class uMESQualityRecordBusiness
    Implements uMESQualityRecordIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()
    Private CheckMainObject As uMESCheckMainBusiness = New uMESCheckMainBusiness()
    Private ScrapInfoObject As uMESScrapInfoBusiness = New uMESScrapInfoBusiness()

    ''' <summary>
    ''' 根据批次和工序获取质量记载单信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetQualityRecordInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev, strWorkReportID As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strWorkReportID = ""
            If QueryCondition.Keys.Contains("WorkReportID") = True Then
                strWorkReportID = QueryCondition("WorkReportID").ToString()
            End If
            strSQL = "SELECT q.Qualityrecordname, c.Containername, sb.Specname, q.Qty AS QualityQty,f.factoryname,s.Specrevision," & vbCrLf & _
        " e1.Fullname, TO_CHAR(q.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, c.Qty,e2.employeename technologistemployee,q.technologistemployeeid, " & vbCrLf & _
        " (wp.Qty - NVL(CEligible.Eligibilityqty,0)-NVL(CEligible.Nonsenseqty,0)) AS NeedCheckQty," & vbCrLf & _
        "q.Recordstatus,DECODE(q.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS RecordstatusName" & vbCrLf & _
        "FROM Qualityrecord q " & vbCrLf & _
        "LEFT JOIN Container c ON c.Containerid = q.Containerid" & vbCrLf & _
        "LEFT JOIN currentstatus cts ON cts.currentstatusid= c.currentstatusid" & vbCrLf & _
        "LEFT JOIN factory f on f.factoryid =cts.factoryid" & vbCrLf & _
        "LEFT JOIN Spec s ON s.Specid = q.Specid" & vbCrLf & _
        "LEFT JOIN Specbase sb ON sb.Specbaseid = s.Specbaseid" & vbCrLf & _
        "LEFT JOIN Employee e1 ON e1.Employeeid = q.Checkemployeeid " & vbCrLf & _
        "LEFT JOIN Employee e2 ON e2.Employeeid = q.technologistemployeeid " & vbCrLf & _
        "LEFT JOIN Workingreporthistory wp on wp.Workingreporthistoryid=q.Workingreportid" & vbCrLf & _
        "LEFT JOIN (SELECT ch.Workingreportid, SUM(ch.Eligibilityqty) AS Eligibilityqty, SUM(ch.Nonsenseqty) AS Nonsenseqty" & vbCrLf & _
        "FROM Conventioncheckhistory ch GROUP BY ch.Workingreportid) CEligible " & _
        " ON CEligible.Workingreportid = wp.Workingreporthistoryid " & _
        "WHERE q.Recordstatus = 1 AND c.ContainerName = '" & strContainerName & "'" & vbCrLf & _
        "AND sb.Specname = '" & strSpecName & "'" & vbCrLf & _
        "AND s.Specrevision = '" & strSpecRev & "'" & vbCrLf
            If strWorkReportID <> "" Then
                strSQL = strSQL & "AND q.Workingreportid = '" & strWorkReportID & "'" & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY q.Checkdate DESC" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取零件序号的质量信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordInfoForProductNo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetQualityRecordInfoForProductNo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strQualityRecordName As String
            strQualityRecordName = ""
            If QueryCondition.Keys.Contains("Qualityrecordname") = True Then
                strQualityRecordName = QueryCondition("Qualityrecordname").ToString()
            End If

            strSQL = "SELECT Productnoinfo.Productno, Productnoinfo.Qualityrecordnotes" & vbCrLf & _
                    "FROM Qualityrecord" & vbCrLf & _
                    "INNER JOIN Qualityrecordproductnoinfo ON Qualityrecord.Qualityrecordid = Qualityrecordproductnoinfo.Qualityrecordid" & vbCrLf & _
                    "INNER JOIN Productnoinfo ON Qualityrecordproductnoinfo.Productnoinfoid = Productnoinfo.Productnoinfoid " & vbCrLf & _
                    "WHERE Qualityrecord.QualityrecordName = '" & strQualityRecordName & "'" & vbCrLf & _
                    "ORDER BY Productnoinfo.Productno" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    Private Function GetNonsensePartNoForSpecCheck(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetNonsensePartNoForSpecCheck
        GetNonsensePartNoForSpecCheck = New uMESProcessResult
        Try
            Dim strSQL, strWorkingreportid As String
            strWorkingreportid = ""
            If QueryCondition.Keys.Contains("WorkReportID") = True Then
                strWorkingreportid = QueryCondition("WorkReportID").ToString()
            End If

            strSQL = "SELECT c.Workingreportid, cpl.PartNO, cpl.Checkresulttype,wp.productnoinfoid" & vbCrLf & _
             " FROM ConventionCheckhistory c" & vbCrLf & _
             "left join ConventionCheckHistoryCheckPar cp" & vbCrLf & _
             "  ON cp.ConventionCheckhistoryid = c.ConventionCheckhistoryid" & vbCrLf & _
             "left join CheckPartNOList cpl" & vbCrLf & _
             "  ON cpl.Checkpartnolistid = cp.Checkpartnolistid" & vbCrLf & _
             "left join (select q.Workingreportid,p.productnoinfoid,p.productno from Qualityrecord q " & vbCrLf & _
             "left join Qualityrecordproductnoinfo qp ON qp.Qualityrecordid = q.Qualityrecordid" & vbCrLf & _
             " left join Productnoinfo p ON p.Productnoinfoid = qp.Productnoinfoid ) wp " & vbCrLf & _
             " on wp.Workingreportid=c.Workingreportid and wp.productno=cpl.partno" & vbCrLf & _
             "WHERE cpl.Checkresulttype=2 and c.Workingreportid = '" & strWorkingreportid & "'" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            GetNonsensePartNoForSpecCheck.DBTable = dtMain
            GetNonsensePartNoForSpecCheck.Result = True
        Catch ex As Exception
            GetNonsensePartNoForSpecCheck.Result = False
            GetNonsensePartNoForSpecCheck.Message = ex.Message
        End Try
    End Function

    Private Function GetNonsensePartNo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetNonsensePartNo
        GetNonsensePartNo = New uMESProcessResult
        Try
            Dim strSQL, strWorkingreportid As String
            strWorkingreportid = ""
            If QueryCondition.Keys.Contains("WorkReportID") = True Then
                strWorkingreportid = QueryCondition("WorkReportID").ToString()
            End If

            strSQL = "SELECT c.Workingreportid, cpl.PartNO, cpl.Checkresulttype,wp.productnoinfoid" & vbCrLf & _
             " FROM ConventionCheckhistory c" & vbCrLf & _
             "left join ConventionCheckHistoryCheckPar cp" & vbCrLf & _
             "  ON cp.ConventionCheckhistoryid = c.ConventionCheckhistoryid" & vbCrLf & _
             "left join CheckPartNOList cpl" & vbCrLf & _
             "  ON cpl.Checkpartnolistid = cp.Checkpartnolistid" & vbCrLf & _
             "left join (select q.Workingreportid,p.productnoinfoid,p.productno from Qualityrecord q " & vbCrLf & _
             "left join Qualityrecordproductnoinfo qp ON qp.Qualityrecordid = q.Qualityrecordid" & vbCrLf & _
             " left join Productnoinfo p ON p.Productnoinfoid = qp.Productnoinfoid ) wp " & vbCrLf & _
             " on wp.Workingreportid=c.Workingreportid and wp.productno=cpl.partno" & vbCrLf & _
             "WHERE cpl.Checkresulttype=2 and wp.productnoinfoid is null and c.Workingreportid = '" & strWorkingreportid & "'" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            GetNonsensePartNo.DBTable = dtMain
            GetNonsensePartNo.Result = True
        Catch ex As Exception
            GetNonsensePartNo.Result = False
            GetNonsensePartNo.Message = ex.Message
        End Try
    End Function
    ''' <summary>
    ''' 获取当天最大流水号的质量记载单
    ''' </summary>
    ''' <returns></returns>
    Private Function GetLastQualityRecord(ByVal QueryCondition As Dictionary(Of String, String)) As String Implements uMESQualityRecordIBusiness.GetLastQualityRecord
        Dim oResult As New uMESProcessResult
        Dim strSQL, strPrefix As String
        GetLastQualityRecord = ""
        strSQL = "SELECT Qualityrecord.Qualityrecordname" & vbCrLf & _
                "FROM Qualityrecord" & vbCrLf & _
                "WHERE Qualityrecord.QualityrecordName LIKE '" & Now.ToString("yyyyMMdd") & "%'" & vbCrLf & _
                "ORDER BY Qualityrecord.Qualityrecordname DESC" & vbCrLf

        Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
        strPrefix = Now.ToString("yyyyMMdd") & "_"
        If dtMain.Rows.Count > 0 Then
            Dim strLastQualityRecordName, strIndex As String
            Dim intIndex As Integer
            strLastQualityRecordName = dtMain.Rows(0).Item("Qualityrecordname")
            intIndex = CInt(Trim(strLastQualityRecordName).Replace(strPrefix, ""))
            strIndex = Format(intIndex + 1, "00000")
            GetLastQualityRecord = strPrefix & strIndex
        Else
            GetLastQualityRecord = strPrefix & "00001"
        End If

    End Function

    ''' <summary>
    ''' 创建质量记载单
    ''' </summary>
    ''' <param name="oQualityRecordDTO">质量记载单创建所需的基础信息</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateQualityRecord(ByVal oQualityRecordDTO As uMESQualityRecordDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESQualityRecordIBusiness.CreateQualityRecord
        CreateQualityRecord = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oQualityRecordDTO.ServerHostInfo

            Dim strTxnDocName, strTxnName, strEventName, strSubentityListName As String
            strTxnDocName = "QualityRecordMaintDoc"
            strTxnName = "QualityRecordMaint"
            strEventName = "New"
            strSubentityListName = "ProductNoInfo"

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Name"
            dataEntity.ClientDataValue = oQualityRecordDTO.QualityRecordName
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Qty"
            dataEntity.ClientDataValue = oQualityRecordDTO.QualityQty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = oQualityRecordDTO.ContainerName
            dataEntity.ClientOtherValue = ""
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            Dim strSpecName As String = oQualityRecordDTO.SpecName
            Dim strSpecRev As String = oQualityRecordDTO.SpecRevision

            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Spec"
            dataEntity.ClientDataValue = strSpecName
            If strSpecRev <> "" Then
                dataEntity.ClientDataIsROR = False
                dataEntity.ClientDataVersion = strSpecRev
            Else
                dataEntity.ClientDataIsROR = True
                dataEntity.ClientDataVersion = strSpecRev
            End If
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "CheckEmployee"
            dataEntity.ClientDataValue = oQualityRecordDTO.CheckEmployee
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "TechnologistEmployee"
            dataEntity.ClientDataValue = oQualityRecordDTO.TechnologistEmployee
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "CheckDate"
            dataEntity.ClientDataValue = oQualityRecordDTO.CheckDate
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Recordstatus"
            dataEntity.ClientDataValue = oQualityRecordDTO.Recordstatus
            m_DataList.Add(dataEntity)

            If oQualityRecordDTO.WorkingReportID <> "" Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                dataEntity.ClientDataName = "WorkingReportID"
                dataEntity.ClientDataValue = oQualityRecordDTO.WorkingReportID
                m_DataList.Add(dataEntity)
            End If

            If oQualityRecordDTO.SynergicInfoName <> "" Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                dataEntity.ClientDataName = "SynergicInfo"
                dataEntity.ClientDataValue = oQualityRecordDTO.SynergicInfoName
                m_DataList.Add(dataEntity)
            End If

            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim dtChildList As DataTable = oQualityRecordDTO.ProductNoInfo

            If dtChildList IsNot Nothing Then
                If dtChildList.Rows.Count > 0 Then
                    For i As Integer = 0 To dtChildList.Rows.Count - 1
                        Dim m_Child As New List(Of ClientAPIEntity)
                        Dim stepEntity As New ClientAPIEntity

                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "ProductNo"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("ProductNo").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "QualityRecordNotes"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("QualityRecordNotes").ToString
                        m_Child.Add(stepEntity)

                        m_ChildList.Add(i.ToString, m_Child)
                    Next
                End If
            End If

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)
            success = clientData.CreateModelHasSubentityList(strTxnDocName, strTxnName, strEventName, m_DataList, strSubentityListName, m_ChildList, strInfo)
            '创建批次结束

            If success = True Then
                CreateQualityRecord = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 修改质量记载单
    ''' </summary>
    ''' <param name="oQualityRecordDTO">质量记载单修改所需的基础信息</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateQualityRecord(ByVal oQualityRecordDTO As uMESQualityRecordDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESQualityRecordIBusiness.UpdateQualityRecord
        UpdateQualityRecord = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oQualityRecordDTO.ServerHostInfo

            Dim strTxnDocName, strTxnName, strEventName, strSubentityListName As String
            strTxnDocName = "QualityRecordMaintDoc"
            strTxnName = "QualityRecordMaint"
            strEventName = "Load"
            strSubentityListName = "ProductNoInfo"

            Dim strUpdateName, strUpdateVersion, strUpdateCDOType As String
            strUpdateName = oQualityRecordDTO.QualityRecordName
            strUpdateVersion = ""
            strUpdateCDOType = "NDO"

            Dim intSubentityListCount As Integer
            intSubentityListCount = oQualityRecordDTO.SubentityListCount

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "RecordStatus"
            dataEntity.ClientDataValue = oQualityRecordDTO.Recordstatus
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Qty"
            dataEntity.ClientDataValue = oQualityRecordDTO.QualityQty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "TechnologistEmployee"
            dataEntity.ClientDataValue = oQualityRecordDTO.TechnologistEmployee
            m_DataList.Add(dataEntity)

            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim dtChildList As DataTable = oQualityRecordDTO.ProductNoInfo

            If dtChildList IsNot Nothing Then
                If dtChildList.Rows.Count > 0 Then
                    For i As Integer = 0 To dtChildList.Rows.Count - 1
                        Dim m_Child As New List(Of ClientAPIEntity)
                        Dim stepEntity As New ClientAPIEntity

                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "ProductNo"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("ProductNo").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "QualityRecordNotes"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("QualityRecordNotes").ToString
                        m_Child.Add(stepEntity)

                        m_ChildList.Add(i.ToString, m_Child)
                    Next
                End If
            End If

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)
            success = clientData.UpdateModelHasSubentityList(strTxnDocName, strTxnName, strUpdateName, strUpdateVersion, _
                                                            strUpdateCDOType, m_DataList, strSubentityListName, m_ChildList, intSubentityListCount, strInfo)

            If success = True Then
                UpdateQualityRecord = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 根据批次和工序获取质量记载单信息(包含零件信息)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordDetailInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetQualityRecordDetailInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strSpecName, strSpecRev, strQualityrecordid As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If

            strQualityrecordid = ""
            If QueryCondition.Keys.Contains("Qualityrecordid") = True Then
                strQualityrecordid = QueryCondition("Qualityrecordid").ToString()
            End If

            strSQL = "SELECT Factory.FactoryName, Qualityrecord.Qualityrecordname, Container.Containername, Specbase.Specname, Qualityrecord.Qty, " & vbCrLf & _
                    "        (CASE WHEN Productnoinfo.Productno = '00000000' THEN '' ELSE Productnoinfo.Productno END) AS Productno, Productnoinfo.Qualityrecordnotes," & vbCrLf & _
                    "        DECODE(Qualityrecord.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS Recordstatus," & vbCrLf & _
                    "        Employee.Fullname AS CheckerName, TO_CHAR(Qualityrecord.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate," & vbCrLf & _
                    "        GYEmployee.Fullname AS TechnologistName, TO_CHAR(Qualityrecord.Technologistdate,'RRRR-MM-DD HH24:MI:SS') AS Technologistdate," & vbCrLf & _
                    "	     QualityDisposeAdvice.Qualitydisposeadvicename, Productnoinfo.Qualitydisposenotes" & vbCrLf & _
                    "FROM Qualityrecord" & vbCrLf & _
                    "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
                    "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid " & vbCrLf & _
                    "LEFT OUTER JOIN Productnoinfo ON Qualityrecord.Qualityrecordid = Productnoinfo.Qualityrecordid" & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee GYEmployee ON GYEmployee.Employeeid = Qualityrecord.disposetechemployeeid" & vbCrLf & _
                    "LEFT OUTER JOIN QualityDisposeAdvice ON QualityDisposeAdvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid " & vbCrLf & _
                    "WHERE Qualityrecord.Recordstatus <> 4 " & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If
            If strQualityrecordid <> "" Then
                strSQL = strSQL & "AND Qualityrecord.Qualityrecordid = '" & strQualityrecordid & "'" & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY Qualityrecord.Checkdate DESC" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordDetailInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 录入检测项界面获取检测项信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordValidateInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetQualityRecordValidateInfo
        Dim oResult As New uMESProcessResult

        Try

            Dim strSQL, strContainerName, strSpecName, strSpecRev, strQualityRecordName, strRecordstatus As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If
            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strQualityRecordName = ""
            If QueryCondition.Keys.Contains("QualityRecordName") Then
                strQualityRecordName = QueryCondition("QualityRecordName").ToString()
            End If
            strRecordstatus = ""
            If QueryCondition.Keys.Contains("Recordstatus") Then
                strRecordstatus = QueryCondition("Recordstatus").ToString()
            End If
            strSQL = "SELECT Qualityrecord.Qualityrecordid, Qualityrecord.Qualityrecordname, Container.Containername, Specbase.Specname, Qualityrecord.Qty AS QualityQty, " & vbCrLf & _
            "       Spec.Specrevision, Employee.Fullname, TO_CHAR(Qualityrecord.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, Container.Qty, " & vbCrLf & _
            "       DECODE(Qualityrecord.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS RecordstatusDisp, Qualityrecord.Recordstatus, Productbase.ProductName, " & vbCrLf & _
            "       TechnologistEmployee.FullName AS TechnologistName, Qualityrecord.Technologistdate, Qualityrecord.questionno, Qualityrecord.solvenotes, Qualityrecord.CheckValidateNotes " & vbCrLf & _
            "FROM Qualityrecord" & vbCrLf & _
            "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
            "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
            "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
            "INNER JOIN Product ON Container.Productid = Product.Productid " & _
            "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid " & _
            "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid " & vbCrLf & _
            "LEFT OUTER JOIN Employee TechnologistEmployee ON TechnologistEmployee.Employeeid = Qualityrecord.DisposeTechEmployeeID " & vbCrLf & _
            "WHERE 1=1 " & vbCrLf
            If strRecordstatus <> "" Then
                strSQL = strSQL & "AND Qualityrecord.Recordstatus in (" & strRecordstatus & ")" & vbCrLf
            End If
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If
            If strQualityRecordName <> "" Then
                strSQL = strSQL & "AND Qualityrecord.Qualityrecordname = '" & strQualityRecordName & "'" & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY Qualityrecord.Recordstatus DESC, Qualityrecord.Checkdate DESC" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)

            strSQL = "SELECT Qualityrecord.Qualityrecordid, (CASE WHEN Productnoinfo.Productno = '00000000' THEN '' ELSE Productnoinfo.Productno END) AS Productno," & vbCrLf & _
            "       Productnoinfo.Qualityrecordnotes, Qualitydisposeadvice.Qualitydisposeadvicename, Productnoinfo.Qualitydisposenotes,(CASE WHEN Productnoinfo.Qty IS NULL THEN 1 ELSE Productnoinfo.Qty END) AS ScrapQty, " & vbCrLf & _
            "       (CASE WHEN ScrapDetails.ProductNo IS NOT NULL THEN '已报废' ELSE '未报废' END) AS Status" & vbCrLf & _
            "       ,(CASE WHEN Productnoinfo.Productno = '00000000' THEN '' ELSE REPLACE(Productnoinfo.Productno,Container.Containername || '-','') END) AS ProductNoDisplay" & vbCrLf & _
            "FROM Qualityrecord" & vbCrLf & _
            "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
            "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
            "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
            "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid" & vbCrLf & _
            "LEFT OUTER JOIN QualityRecordProductNoInfo ON QualityRecordProductNoInfo.Qualityrecordid = Qualityrecord.Qualityrecordid" & vbCrLf & _
            "LEFT OUTER JOIN Productnoinfo ON Productnoinfo.Productnoinfoid = QualityRecordProductNoInfo.Productnoinfoid" & vbCrLf & _
            "LEFT OUTER JOIN Qualitydisposeadvice ON Productnoinfo.Qualitydisposeadviceid = Qualitydisposeadvice.Qualitydisposeadviceid" & vbCrLf & _
            "LEFT OUTER JOIN ScrapDetails ON ScrapDetails.ProductNo = Productnoinfo.Productno" & vbCrLf & _
            "WHERE 1=1 " & vbCrLf
            If strRecordstatus <> "" Then
                strSQL = strSQL & "AND Qualityrecord.Recordstatus in (" & strRecordstatus & ")" & vbCrLf
            End If
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            If strSpecName <> "" Then
                strSQL = strSQL & "AND Specbase.Specname = '" & strSpecName & "'" & vbCrLf
            End If
            If strSpecRev <> "" Then
                strSQL = strSQL & "AND Spec.Specrevision = '" & strSpecRev & "'" & vbCrLf
            End If
            If strQualityRecordName <> "" Then
                strSQL = strSQL & "AND Qualityrecord.Qualityrecordname = '" & strQualityRecordName & "'" & vbCrLf
            End If

            Dim dtDetail As DataTable = dal.GetCheckPointResult(strSQL)

            Dim ds As New DataSet
            ds.Tables.Add(dtMain.Copy())
            ds.Tables(0).TableName = "dtMain"
            ds.Tables.Add(dtDetail.Copy())
            ds.Tables(1).TableName = "dtDetail"

            Dim parentColunms = New DataColumn(0) {ds.Tables(0).Columns("Qualityrecordid")}
            Dim childColunms = New DataColumn(0) {ds.Tables(1).Columns("Qualityrecordid")}
            ds.Relations.Add("rs0", parentColunms, childColunms)

            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 质量记载单工艺处理
    ''' </summary>
    ''' <param name="oQualityRecordDTO">质量记载单工艺处理所需的基础信息</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function QualityRecordDispose(ByVal oQualityRecordDTO As uMESQualityRecordDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESQualityRecordIBusiness.QualityRecordDispose
        QualityRecordDispose = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oQualityRecordDTO.ServerHostInfo

            Dim strTxnDocName, strTxnName, strEventName As String
            strTxnDocName = "QualityRecordMaintDoc"
            strTxnName = "QualityRecordMaint"
            strEventName = "Load"

            Dim strUpdateName, strUpdateVersion, strUpdateCDOType As String
            strUpdateName = oQualityRecordDTO.QualityRecordName
            strUpdateVersion = ""
            strUpdateCDOType = "NDO"

            Dim intSubentityListCount As Integer
            intSubentityListCount = oQualityRecordDTO.SubentityListCount

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "TechnologistEmployee"
            dataEntity.ClientDataValue = daServerHost.UserName
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "TechnologistDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "TechnologistUrl"
            dataEntity.ClientDataValue = oQualityRecordDTO.TechnologistUrl
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "RecordStatus"
            dataEntity.ClientDataValue = 2
            m_DataList.Add(dataEntity)

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)
            success = clientData.UpdateModelByStandard(strTxnDocName, strTxnName, strUpdateName, strUpdateVersion, _
                                                             m_DataList, strInfo)

            If success = True Then
                QualityRecordDispose = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 质量记载单确认
    ''' </summary>
    ''' <param name="oQualityRecordDTO">质量记载单确认所需的基础信息</param>
    ''' <param name="result">执行结果</param>
    ''' <param name="message">返回结果信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function QualityRecordValidate(ByVal oQualityRecordDTO As uMESQualityRecordDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESQualityRecordIBusiness.QualityRecordValidate
        QualityRecordValidate = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = oQualityRecordDTO.ServerHostInfo

            Dim strTxnDocName, strTxnName, strEventName As String
            strTxnDocName = "QualityRecordMaintDoc"
            strTxnName = "QualityRecordMaint"
            strEventName = "Load"

            Dim strUpdateName, strUpdateVersion, strUpdateCDOType As String
            strUpdateName = oQualityRecordDTO.QualityRecordName
            strUpdateVersion = ""
            strUpdateCDOType = "NDO"

            Dim intSubentityListCount As Integer
            intSubentityListCount = oQualityRecordDTO.SubentityListCount

            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "CheckValidateEmployee"
            dataEntity.ClientDataValue = daServerHost.UserName
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "CheckValidateDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "RecordStatus"
            dataEntity.ClientDataValue = 3
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "questionno"
            dataEntity.ClientDataValue = oQualityRecordDTO.QuenstionNO
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "solvenotes"
            dataEntity.ClientDataValue = oQualityRecordDTO.SolveNotes
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "notes"
            dataEntity.ClientDataValue = oQualityRecordDTO.Notes
            m_DataList.Add(dataEntity)

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)
            success = clientData.UpdateModelByStandard(strTxnDocName, strTxnName, strUpdateName, strUpdateVersion, _
                                                             m_DataList, strInfo)

            If success = True Then
                QualityRecordValidate = True
                result = True
            End If

            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    Public Function GetTechnologistEmployee(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetTechnologistEmployee
        GetTechnologistEmployee = New uMESProcessResult
        Try
            Dim strSQL, strContainerName As String
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If

            strSQL = String.Format("select t.employeename,t.fullname,f.factoryname,r.rolename from employee t" & vbCrLf & _
                    "left join sessionvalues sv on sv.sessionvaluesid=t.sessionvaluesid" & vbCrLf & _
                    "left join factory f on f.factoryid=sv.factoryid" & vbCrLf & _
                    "left join employeerole er on er.employeeid=t.employeeid" & vbCrLf & _
                    "left join (select f.factoryid,f.factoryname,'工艺员' frolename from Container c " & vbCrLf & _
                    "left join  currentstatus cts on cts.currentstatusid= c.currentstatusid " & vbCrLf & _
                    "left join  factory f on f.factoryid =cts.factoryid " & vbCrLf & _
                    "where c.containername='{0}')cf on cf.factoryid=sv.factoryid " & vbCrLf & _
                    "left join roledef r on r.roleid =er.roleid and r.rolename=cf.frolename" & vbCrLf & _
                    "where r.rolename is not null", strContainerName)

            Dim dtMain As DataTable = dal.GetQualityRecordDetailInfo(strSQL)
            GetTechnologistEmployee.DBTable = dtMain
            GetTechnologistEmployee.Result = True
        Catch ex As Exception
            GetTechnologistEmployee.Result = False
            GetTechnologistEmployee.Message = ex.Message
        End Try
    End Function

    Public Function GetFiveBillsType() As uMESProcessResult Implements uMESQualityRecordIBusiness.GetFiveBillsType
        GetFiveBillsType = New uMESProcessResult()
        Try
            Dim strSQL As String

            strSQL = "select fbt.fivebilltypeid,fbt.fivebilltypename from fivebilltype fbt"

            Dim dtMain As DataTable = dal.GetQualityRecordDetailInfo(strSQL)
            GetFiveBillsType.DBTable = dtMain
            GetFiveBillsType.Result = True
        Catch ex As Exception
            GetFiveBillsType.Result = False
            GetFiveBillsType.Message = ex.Message
        End Try
    End Function

    Public Function GeetFiveBillsInfo(ByVal QualityRecordName As String) As uMESProcessResult Implements uMESQualityRecordIBusiness.GeetFiveBillsInfo
        GeetFiveBillsInfo = New uMESProcessResult()
        Try
            Dim strSQL As String

            strSQL = "select t1.*,t2.qualityrecordname " & vbCrLf & _
                     "from fivebillsinfo t1 " & vbCrLf & _
                     "left join qualityrecord t2 on t1.qualityrecordid = t2.qualityrecordid " & vbCrLf & _
                     "where t2.qualityrecordname ='" & QualityRecordName & "'"

            Dim dtMain As DataTable = dal.GetQualityRecordDetailInfo(strSQL)
            GeetFiveBillsInfo.DBTable = dtMain
            GeetFiveBillsInfo.Result = True
        Catch ex As Exception
            GeetFiveBillsInfo.Result = False
            GeetFiveBillsInfo.Message = ex.Message
        End Try
    End Function

    ''' <summary>
    ''' 根据报工记录计算不合格数（报工数量-检验记录的合格数）
    ''' </summary>
    ''' <returns></returns>
    Private Function GetConventioncheckhistoryInfo(ByVal oQualityRecordDTO As uMESQualityRecordDTO) As uMESQualityRecordDTO Implements uMESQualityRecordIBusiness.GetConventioncheckhistoryInfo
        Dim oResult As New uMESProcessResult
        Dim oInfo As New uMESQualityRecordDTO
        Dim strSQL, strContainerName, strSpecName, strWorkReportID As String

        Try
            oInfo = oQualityRecordDTO
            strWorkReportID = ""
            strSpecName = ""
            strContainerName = ""

            strContainerName = oQualityRecordDTO.ContainerName.ToString()
            strSpecName = oQualityRecordDTO.SpecName.ToString()
            strWorkReportID = oQualityRecordDTO.WorkingReportID.ToString()

            If strWorkReportID <> "" Then
                strSQL = "SELECT * FROM conventioncheckhistory WHERE conventioncheckhistory.workingreportid = '" & strWorkReportID & "'"
                Dim DT As DataTable = dal.GetContainerInfo(strSQL)

                If DT.Rows.Count > 0 Then
                    Dim boolResult As Boolean
                    Dim strMessage As String = ""
                    oInfo.QualityQty = oInfo.ReportQty - DT.Rows(0).Item("eligibilityqty")
                    CreateQualityRecord(oInfo, boolResult, strMessage)
                Else
                    oInfo.QualityQty = oInfo.ReportQty
                End If

            End If

        Catch ex As Exception

        End Try

        Return oInfo
    End Function

    ''' <summary>
    ''' 根据批次和工序获取不合格品审理单信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordInfo2(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESQualityRecordIBusiness.GetQualityRecordInfo2
        Dim oResult As New uMESProcessResult
        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()
        Try
            Dim strSQL, strContainerName, strSpecName, strSpecNo, strSpecRev, strWorkReportID, strProductName As String
            strContainerName = ""
            Dim strTogetherFlag As String = ""
            ' CheckMainObject = New uMESCheckMainBusiness

            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString
                If strContainerName.Trim() <> "" Then

                    Dim r As DataTable = CheckMainObject.GetContainerInfo(strContainerName)
                    If r.Rows(0).IsNull("TogetherFlag") = False Then
                        strTogetherFlag = r.Rows(0)("TogetherFlag").ToString
                    End If
                End If
            End If

            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strSpecName = ""
            If QueryCondition.Keys.Contains("SpecName") = True Then
                strSpecName = QueryCondition("SpecName").ToString()
            End If

            strSpecNo = ""
            If QueryCondition.Keys.Contains("specno") = True Then
                strSpecNo = QueryCondition("specno").ToString()
            End If

            strSpecRev = ""
            If QueryCondition.Keys.Contains("SpecRev") = True Then
                strSpecRev = QueryCondition("SpecRev").ToString()
            End If
            strWorkReportID = ""
            If QueryCondition.Keys.Contains("WorkReportID") = True Then
                strWorkReportID = QueryCondition("WorkReportID").ToString()
            End If
            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            If strProductName.Trim() = "" Then
                If QueryCondition.Keys.Contains("ContainerName") = True Then
                    strContainerName = commBusiness.ReplaceContainerName(QueryCondition("ContainerName").ToString.Trim())
                    If strContainerName.Trim() <> "" Then
                        Dim r As DataTable = CheckMainObject.GetContainerInfo(strContainerName)
                        If r.Rows(0).IsNull("TogetherFlag") = False Then
                            strTogetherFlag = r.Rows(0)("TogetherFlag").ToString
                            strProductName = r.Rows(0)("productname").ToString
                        End If
                    End If
                End If
            End If
            strSQL = "SELECT q.Qualityrecordname, c.Containername, sb.Specname, q.Qty AS QualityQty,f.factoryname,s.Specrevision AS SpecRev," & vbCrLf & _
                        "e1.Fullname, TO_CHAR(q.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, c.Qty,e2.employeename AS technologistemployee," & vbCrLf & _
                        "q.technologistemployeeid,(wp.Qty - NVL(CEligible.Eligibilityqty,0)-NVL(CEligible.Nonsenseqty,0)) AS NeedCheckQty," & vbCrLf & _
                        "q.Recordstatus,DECODE(q.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS  RecordstatusName," & vbCrLf & _
                        "q.revokenotes,q.notes,q.solvenotes,q.workingreportid,wp.Qty AS ReportQty, " & vbCrLf & _
                        "e3.Fullname AS Reportemployee,e3.employeeid AS ReportemployeeID,hml.systemdate AS Realcompletiondate,q.questionno,pb.productname, p.Productrevision " & vbCrLf & _
                     "FROM Qualityrecord q  " & vbCrLf & _
                        "INNER JOIN Container c ON c.Containerid = q.Containerid " & vbCrLf & _
                        "INNER JOIN currentstatus cts ON cts.currentstatusid= c.currentstatusid " & vbCrLf & _
                        "LEFT JOIN factory f on f.factoryid =cts.factoryid " & vbCrLf & _
                        "INNER JOIN Spec s ON s.Specid = q.Specid " & vbCrLf & _
                        "INNER JOIN Specbase sb ON sb.Specbaseid = s.Specbaseid " & vbCrLf & _
                        "LEFT JOIN Employee e1 ON e1.Employeeid = q.Checkemployeeid " & vbCrLf & _
                        "LEFT JOIN Employee e2 ON e2.Employeeid = q.technologistemployeeid " & vbCrLf & _
                        "INNER JOIN Workingreporthistory wp on wp.Workingreporthistoryid=q.Workingreportid " & vbCrLf & _
                        "left join historymainline hml on hml.historymainlineid=wp.historymainlineid " & vbCrLf & _
                        "LEFT JOIN (SELECT ch.Workingreportid, SUM(ch.Eligibilityqty) AS Eligibilityqty, SUM(ch.Nonsenseqty) AS Nonsenseqty " & vbCrLf & _
                                   "FROM Conventioncheckhistory ch " & vbCrLf & _
                                   "GROUP BY ch.Workingreportid) CEligible ON CEligible.Workingreportid = wp.Workingreporthistoryid " & vbCrLf & _
                        "INNER JOIN Product p ON c.Productid = p.Productid" & vbCrLf & _
                        "INNER JOIN Productbase pb ON p.Productbaseid = pb.Productbaseid " & vbCrLf & _
                        "LEFT JOIN Employee e3 ON e3.Employeeid = wp.reportemployeeid  " & vbCrLf & _
                     "WHERE q.Recordstatus=1 AND q.Recordstatus<>4 "

            If strTogetherFlag.Trim() <> "" Then
                strSQL += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
                strSQL += "AND pb.Productname like '%" & strProductName & "%'" & vbCrLf
            Else
                If strWorkReportID <> "" Then
                    strSQL = strSQL & "AND q.Workingreportid = '" & strWorkReportID & "'" & vbCrLf
                End If

                If strContainerName <> "" Then
                    strSQL = strSQL & "AND c.ContainerName = '" & strContainerName & "'" & vbCrLf
                End If

                If strSpecName <> "" Then
                    strSQL = strSQL & "AND sb.Specname like '%" & strSpecName & "%'" & vbCrLf
                End If

                If strSpecNo <> "" Then
                    strSQL = strSQL & "AND s.Specno = '" & strSpecNo & "'" & vbCrLf
                End If

                If strSpecRev <> "" Then
                    strSQL = strSQL & "AND s.Specrevision = '" & strSpecRev & "'" & vbCrLf
                End If

                If strProductName <> "" Then
                    strSQL = strSQL & "AND pb.ProductName like '%" & strProductName & "%'" & vbCrLf
                End If
                If QueryCondition.Keys.Contains("StartDate") = True Then
                    strSQL += "AND q.checkdate >= TO_DATE('" & QueryCondition("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
                End If
                If QueryCondition.Keys.Contains("EndDate") = True Then
                    strSQL += "AND q.checkdate <= TO_DATE('" & QueryCondition("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
                End If
            End If

            strSQL = strSQL & "ORDER BY q.Checkdate DESC" & vbCrLf

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL

            Dim r2 As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim tmpTable As DataTable = DataAdjust.FormatDataSetSpecNameByColumName("Specname", r2.DBTable).Copy()
            oResult.Result = True
            oResult.DBTable = tmpTable
            oResult.RowCount = r2.RowCount

            'Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            'oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)
            'oResult.Result = True

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 不合格品审理确认
    ''' </summary>
    ''' <returns></returns>
    Public Function NonconformingProductTria(ByVal oQualityRecordDTO As uMESQualityRecordDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESQualityRecordIBusiness.NonconformingProductTria

        Try
            Dim strSql As String = String.Format("UPDATE Qualityrecord t SET t.recordstatus = '3',t.Qty = '{0}', t.questionno = '{1}', t.SolveNotes = '{2}', t.RevokeNotes = '{3}', t.Notes = '{4}', t.TechnologistUrl = '{5}', t.CheckValidateEmployeeId = '{6}', t.CheckValidateDate = to_date('{7}','yyyy-mm-dd hh24:mi:ss') WHERE t.QualityRecordName = '{8}'",
                                                 oQualityRecordDTO.QualityQty,
                                                 oQualityRecordDTO.QuenstionNO,
                                                 oQualityRecordDTO.SolveNotes,
                                                 oQualityRecordDTO.RevokeNotes,
                                                 oQualityRecordDTO.Notes,
                                                 oQualityRecordDTO.TechnologistUrl,
                                                 oQualityRecordDTO.CheckValidateEmployeeId,
                                                 Now.ToString(),
                                                 oQualityRecordDTO.QualityRecordName)
            result = dal.ExecuteSql(strSql)
            Return result
        Catch ex As Exception
            result = False
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 报废后更新质量记载单状态
    ''' </summary>
    ''' <returns></returns>
    Public Function UpdateQualityRecordStatus(ByVal oQualityRecordDTO As uMESQualityRecordDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements uMESQualityRecordIBusiness.UpdateQualityRecordStatus
        Try
            ScrapInfoObject = New uMESScrapInfoBusiness
            Dim totalScarpCount As Integer = ScrapInfoObject.GetTotalScarpCount(oQualityRecordDTO.QualityRecordName)
            Dim qualityQty As Integer = oQualityRecordDTO.QualityQty
            Dim strSql As String = ""
            '累计报废数量等于不合格数量更新质量记载单状态为6
            If totalScarpCount = qualityQty Then
                strSql = String.Format("UPDATE Qualityrecord t SET t.recordstatus = '6', t.CheckValidateEmployeeId= '{0}', t.CheckValidateDate = to_date('{1}','yyyy-mm-dd hh24:mi:ss') WHERE t.QualityRecordName = '{2}'",
                                                     oQualityRecordDTO.CheckValidateEmployeeId,
                                                     Now.ToString(),
                                                     oQualityRecordDTO.QualityRecordName)
            ElseIf totalScarpCount < qualityQty Then
                strSql = String.Format("UPDATE Qualityrecord t SET t.recordstatus = '3', t.CheckValidateEmployeeId= '{0}', t.CheckValidateDate = to_date('{1}','yyyy-mm-dd hh24:mi:ss') WHERE t.QualityRecordName = '{2}'",
                                                     oQualityRecordDTO.CheckValidateEmployeeId,
                                                     Now.ToString(),
                                                     oQualityRecordDTO.QualityRecordName)
            End If
            result = dal.ExecuteSql(strSql)
            Return result
        Catch ex As Exception
            result = False
            Return False
        End Try
    End Function


End Class

